﻿Imports System.Data.SqlClient

Public Class cUser

    Dim con As New SqlConnection(strcon)

    Public Function select_User() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT UserID,Password,Role,EmployeeID,LawyerID FROM tblUser")
        Return dt
    End Function

    Public Function select_User_Lawyer() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT UserID,[Password],[Role],LawyerName FROM vUser_Lawyer WHERE Active=1")
        Return dt
    End Function

    Public Function select_User_Employee()
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT UserID,[Password],[Role],EmployeeName FROM vUser_Employee WHERE Active=1")
        Return dt
    End Function

    Public Function select_User_Lawyer_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT UserID,[Password],[Role],LawyerName FROM vUser_Lawyer WHERE Active=1 AND LOWER(UserID + Role + LawyerName) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function select_User_Employee_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT UserID,[Password],[Role],EmployeeName FROM vUser_Employee WHERE Active=1 AND LOWER(UserID + Role + EmployeeName) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function select_User_byID(ByVal UserID As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT UserID,Password,Role,EmployeeID,LawyerID FROM tblUser WHERE UserID=N'" & UserID & "'")
        Return dt
    End Function

    Public Function insert_User(ByVal UserID As String, ByVal Password As String, ByVal Role As String, ByVal EmployeeID As Object, ByVal LawyerID As Object) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblUser(UserID,[Password],[Role],EmployeeID,LawyerID) VALUES(@UserID,@Password,@Role,@EmployeeID,@LawyerID)"
        With cmd.Parameters
            .AddWithValue("@UserID", UserID)
            .AddWithValue("@Password", Password)
            .AddWithValue("@Role", Role)
            .AddWithValue("@EmployeeID", EmployeeID)
            .AddWithValue("@LawyerID", LawyerID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function update_User(ByVal UserID As String, ByVal Password As String, ByVal Role As String, ByVal EmployeeID As Object, ByVal LawyerID As Object) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblUser SET [Password]=@Password,[Role]=@Role,EmployeeID=@EmployeeID,LawyerID=@LawyerID WHERE UserID=@UserID"
        With cmd.Parameters
            .AddWithValue("@UserID", UserID)
            .AddWithValue("@Password", Password)
            .AddWithValue("@Role", Role)
            .AddWithValue("@EmployeeID", EmployeeID)
            .AddWithValue("@LawyerID", LawyerID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_User(ByVal UserID As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblUser SET Active=0 WHERE UserID=@UserID"
        With cmd.Parameters
            .AddWithValue("@UserID", UserID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function update_User_Password(ByVal UserID As String, ByVal Password As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblUser SET Password=@Password WHERE UserID=@UserID"
        With cmd.Parameters
            .AddWithValue("@UserID", UserID)
            .AddWithValue("@Password", Password)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

End Class
